/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dictionary.dal;

import dictionary.entities.Classify;
import dictionary.entities.Mean;
import dictionary.entities.Word;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author s0c0labd
 */
public class WordDAO {

    private Connection cn;

    public WordDAO() {
    }

    public Word getByWord(String word, int type) {
        cn = ConnectDB.getConnection();
        Word w = new Word();
        List<Mean> l = new ArrayList();
        int id = 0;
        int cid;
        try {
            if (cn.isClosed()) {
                cn = ConnectDB.getConnection();
            }
            PreparedStatement pstm = cn.prepareStatement("SELECT WordID,Word,TypeID FROM Word WHERE Word = ? AND TypeID = ?");
            pstm.setString(1, word);
            pstm.setInt(2, type);
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                w.setWord(rs.getString(2));
                w.setType(rs.getInt(3));
                id = rs.getInt(1);
            }
            rs.close();
            pstm.close();

            if (id != 0) {
                PreparedStatement pstm1 = cn.prepareStatement("SELECT * FROM Mean WHERE WordID = ?");
                pstm1.setInt(1, id);
                ResultSet rs1 = pstm1.executeQuery();
                while (rs1.next()) {
                    cid = rs1.getInt("ClassifyID");
                    PreparedStatement pstm2 = cn.prepareStatement("SELECT ClassifyName FROM Classify WHERE ClassifyID = ?");
                    pstm2.setInt(1, cid);
                    ResultSet rs2 = pstm2.executeQuery();
                    while (rs2.next()) {
                        l.add(new Mean(w, new Classify(rs2.getString(1)), rs1.getString("Mean")));
                    }
                    rs2.close();
                    pstm2.close();
                }
                rs1.close();
                pstm1.close();
                w.setMean(l);
            }
            cn.close();
        } catch (SQLException ex) {
            Logger.getLogger(WordDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return w;
    }

    public List<Word> getAll(int type) {
        cn = ConnectDB.getConnection();
        int cid;
        Map<Integer, String> m = new TreeMap<Integer, String>();
        Map<Integer, String> m1 = new TreeMap<Integer, String>();
        
        List<Word> allword = new ArrayList<Word>();
        try {

            PreparedStatement pstm = cn.prepareStatement("SELECT WordID,Word FROM Word WHERE TypeID=? ORDER BY Word");
            pstm.setInt(1, type);
            List<Integer> listID = new ArrayList<Integer>();
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                m.put(rs.getInt(1), rs.getString(2));
                listID.add(new Integer(rs.getInt(1)));
            }
            rs.close();
            pstm.close();

            pstm = cn.prepareStatement("SELECT ClassifyID,ClassifyName FROM Classify WHERE TypeID=? ORDER BY ClassifyName");
            pstm.setInt(1, type);
            rs = pstm.executeQuery();
            while (rs.next()) {
                m1.put(rs.getInt(1), rs.getString(2));
            }
            rs.close();
            pstm.close();

            for (Integer i : listID) {
                List<Mean> l = new ArrayList<Mean>();
                Word w = new Word(m.get(i), type);
                PreparedStatement pstm1 = cn.prepareStatement("SELECT * FROM Mean WHERE WordID = ?");
                pstm1.setInt(1, i);
                ResultSet rs1 = pstm1.executeQuery();
                while (rs1.next()) {
                    cid = rs1.getInt("ClassifyID");
                    l.add(new Mean(w, new Classify(m1.get(cid), type), rs1.getString("Mean")));
                }
                w.setMean(l);
    
                rs1.close();
                pstm1.close();
                System.out.println(w);
                allword.add(w);
            }
            cn.close();
        } catch (SQLException ex) {
            Logger.getLogger(WordDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
         
        return allword;
    }

    public int insertWord(Word w) {
        cn = ConnectDB.getConnection();
        int rt = 3;
        try {
            CallableStatement cstm = cn.prepareCall("{call spInsertWord(?,?,?)}");
            cstm.registerOutParameter(3, Types.INTEGER);
            cstm.setInt(2, w.getType());
            cstm.setString(1, w.getWord());
            cstm.execute();
            rt = cstm.getInt(3);
            cstm.close();
            cn.close();
        } catch (SQLException ex) {
            Logger.getLogger(WordDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rt;
    }

    public int deleteWord(Word w) {
        cn = ConnectDB.getConnection();
        int rt = 3;
        int rt2 = 0;
        try {
            CallableStatement cstm1 = cn.prepareCall("{call spGetWordID(?,?,?)}");
            cstm1.registerOutParameter(3, Types.INTEGER);
            cstm1.setString(1, w.getWord());
            cstm1.setInt(2, w.getType());
            cstm1.execute();
            int wordID = cstm1.getInt(3);
            cstm1.close();
            if (wordID > 0) {
                CallableStatement cstm2 = cn.prepareCall("{call spDeleteMeanByWord(?,?)}");
                cstm2.registerOutParameter(2, Types.INTEGER);
                cstm2.setInt(1, wordID);
                cstm2.execute();
                rt = cstm2.getInt(2);
                cstm2.close();

                CallableStatement cstm = cn.prepareCall("{call spDeleteWord(?,?,?)}");
                cstm.registerOutParameter(3, Types.INTEGER);
                cstm.setString(1, w.getWord());
                cstm.setInt(2, w.getType());
                cstm.execute();
                rt2 = cstm.getInt(3);
                cstm.close();
                switch (rt) {
                    case 0:
                        if (rt2 == 1) {
                            rt = 1;
                        }
                        break;
                    case 1:
                        if (rt2 == 0) {
                            rt = 0;
                        }
                        break;
                }
            } else {
                rt = 2;
            }
            cn.close();
        } catch (SQLException ex) {
            Logger.getLogger(WordDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rt;
    }

    public int updateWord(Word w, Word w1) {
        cn = ConnectDB.getConnection();
        int rt = 3;
        if (w.getType() != w1.getType()) {
            rt = 2;
        } else {
            try {
                CallableStatement cstm = cn.prepareCall("{call spUpdateWord(?,?,?,?)}");
                cstm.registerOutParameter(4, Types.INTEGER);
                cstm.setString(1, w.getWord());
                cstm.setString(2, w1.getWord());
                cstm.setInt(3, w.getType());
                cstm.execute();
                rt = cstm.getInt(4);
                cstm.close();
                cn.close();
            } catch (SQLException ex) {
                Logger.getLogger(WordDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return rt;
    }
}
